# -*- coding: utf-8 -*-
"""
@File:    receive_dao.py
@Author:  jk.dong
@Desc:    None
"""
from docwork import logging, datetime
from docwork.dao.base_dao import (BaseDao, fit_sql, deal_date, db1_type, db1_address,
                                  db1_name, db1_port, db1_username, db1_password)
from docwork.config import load_config, table_km_rawmaillog, table_entityconfig

# 初始化数据库对象
RECV_DB = BaseDao(db1_type, username=db1_username, password=db1_password, address=db1_address, dbname=db1_name,
                  port=db1_port)
RECV_CONF = load_config(section=['receive-setting', 'import-setting'])


class ReceiveDao:
    @staticmethod
    def get_rawmaillog(message_id, from_=None):
        try:
            if from_:
                if db1_type == 1 or db1_type == 4 or db1_type == 5:
                    sql = (f"SELECT ID,RECVTIME,EMAIL,SUBJECT,MAILTIME,MAILSIZE,MAILID,XINFO FROM {table_km_rawmaillog}"
                           f" WHERE MAILID='{message_id}' AND instr('{from_}', email) > 0")
                else:
                    sql = (f"SELECT ID,RECVTIME,EMAIL,SUBJECT,MAILTIME,MAILSIZE,MAILID,XINFO FROM {table_km_rawmaillog}"
                           f" WHERE MAILID='{message_id}' AND EMAIL LIKE '%{from_}%'")
            else:
                sql = (f"SELECT ID,RECVTIME,EMAIL,SUBJECT,MAILTIME,MAILSIZE,MAILID,XINFO FROM {table_km_rawmaillog}"
                       f" WHERE MAILID='{message_id}'")
            dt_l = RECV_DB.search(sql, cols_info=True)
            dr = deal_date(dt_l)
            if dr:
                dr = dr[0]
            return dr
        except Exception as err:
            logging.error(f"An Exception occurred while getting table km_rawmaillog info. Error: {err}")
            return None

    @staticmethod
    def save_rawmaillog(rawmail_id):
        try:
            current_id = get_entityconfig('KM_RAWMAILLOG')
            if db1_type == 1 or db1_type == 4 or db1_type == 5:
                sql = f"INSERT INTO {table_km_rawmaillog} (ID,MAILID,RECVTIME,XINFO) VALUES ({current_id},'{rawmail_id}',TO_DATE('{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}','yyyy-mm-dd hh24:mi:ss'),'0')"
            elif db1_type == 3:
                sql = f"INSERT INTO {table_km_rawmaillog} (ID,MAILID,RECVTIME,XINFO) VALUES ({current_id},'{rawmail_id}',TO_TIMESTAMP('{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}','yyyy-mm-dd hh24:mi:ss'),'0')"
            else:
                sql = f"INSERT INTO {table_km_rawmaillog} (ID,MAILID,RECVTIME,XINFO) VALUES ({current_id},'{rawmail_id}','{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}','0')"
            result = RECV_DB.update(sql)
            if result:
                return int(current_id)
        except Exception as err:
            logging.error(f"保存km_rawmaillog表时发生异常（对应邮件mailid为{rawmail_id}）。 Error: {err}")
            return None

    @staticmethod
    def update_rawmaillog(rawmail_id, rawmaillog_id, msg):
        """更新邮件原始标题、邮件来源和发送地址"""
        if db1_type == 1 or db1_type == 4 or db1_type == 5:
            sql = (f"UPDATE {table_km_rawmaillog} SET EMAIL=" + fit_sql('email') + ",MAILTO=" + fit_sql(
                'mailto') + ",SUBJECT=" + fit_sql('subject') +
                   f",RECVTIME=TO_DATE('{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}','yyyy-mm-dd hh24:mi:ss'),MAILID='{rawmail_id}',MAILTIME=TO_DATE('{msg.receive_time}','yyyy-mm-dd hh24:mi:ss'),MAILSIZE='{len(msg.content)}' WHERE ID={rawmaillog_id}")
        elif db1_type == 3:
            sql = (f"UPDATE {table_km_rawmaillog} SET EMAIL=" + fit_sql('email') + ",MAILTO=" + fit_sql(
                'mailto') + ",SUBJECT=" + fit_sql('subject') +
                   f",RECVTIME=TO_TIMESTAMP('{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}','yyyy-mm-dd hh24:mi:ss'),MAILID='{rawmail_id}',MAILTIME=TO_TIMESTAMP('{msg.receive_time}','yyyy-mm-dd hh24:mi:ss'),MAILSIZE='{len(msg.content)}' WHERE ID={rawmaillog_id}")
        else:
            sql = (f"UPDATE {table_km_rawmaillog} SET EMAIL=" + fit_sql('email') + ",MAILTO=" + fit_sql(
                'mailto') + ",SUBJECT=" + fit_sql('subject') +
                   f",RECVTIME='{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}',MAILID='{rawmail_id}',MAILTIME='{msg.receive_time.strftime('%Y-%m-%d %H:%M:%S')}',MAILSIZE='{len(msg.content)}' WHERE ID={rawmaillog_id}")

        values = {
            'email': str(msg.from_),
            'mailto': str(msg.to_),
            'subject': str(msg.subject)
        }
        if db1_type == 4:
            sql = sql.format(**values)
            values = {}
        RECV_DB.update(sql, params=values)

    @staticmethod
    def delete_rawmaillog(rawmaillog_id):
        sql = f"DELETE FROM {table_km_rawmaillog} WHERE ID={rawmaillog_id}"
        RECV_DB.update(sql)
        logging.info("Table km_rawmaillog records have been deleted.")

    @staticmethod
    def get_similar_mail(subject):
        current_time = (datetime.datetime.now() - datetime.timedelta(days=0.1)).strftime(
            '%Y-%m-%d %H:%M:%S')
        if db1_type == 1 or db1_type == 4 or db1_type == 5:
            mail_title_sql = f"SELECT SUBJECT FROM {table_km_rawmaillog} WHERE SUBJECT=" + fit_sql(
                'subject') + f" AND RECVTIME>TO_DATE('{current_time}','yyyy-mm-dd hh24:mi:ss')"
        elif db1_type == 3:
            mail_title_sql = f"SELECT SUBJECT FROM {table_km_rawmaillog} WHERE SUBJECT=" + fit_sql(
                'subject') + f" AND RECVTIME>TO_TIMESTAMP('{current_time}','yyyy-mm-dd hh24:mi:ss')"
        else:
            mail_title_sql = f"SELECT SUBJECT FROM {table_km_rawmaillog} WHERE SUBJECT=" + fit_sql(
                'subject') + f" AND RECVTIME>'{current_time}'"
        values = {'subject': str(subject)}
        if db1_type == 4:
            mail_title_sql = mail_title_sql.format(**values)
            values = {}
        mail_title = RECV_DB.search(mail_title_sql, params=values)
        return mail_title

    @staticmethod
    def update_rawmaillog_xinfo(rawmaillog_id, status):
        sql = f"UPDATE {table_km_rawmaillog} SET XINFO={status} WHERE ID={rawmaillog_id}"
        RECV_DB.update(sql)


def get_entityconfig(table_name, con=None):
    now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    sql = f"SELECT CURRENT_ID FROM {table_entityconfig} WHERE NAME='{table_name}'"
    if con:
        current_id = RECV_DB.get_info(conn=con, sql=sql)
    else:
        current_id = RECV_DB.search(sql)
    if not current_id:
        insert_sql = ""
        if db1_type == 1 or db1_type == 4 or db1_type == 5:
            insert_sql = (f"insert into {table_entityconfig} (name,begin_id,step,current_id,create_time,update_time)"
                          f" values ('{table_name}', 1, 1, 1, to_date('{now_time}','yyyy-mm-dd hh24:mi:ss'), to_date('{now_time}','yyyy-mm-dd hh24:mi:ss'))")
        elif db1_type == 2:
            insert_sql = (f"insert into {table_entityconfig} (name,begin_id,step,current_id,create_time,update_time)"
                          f" values ('{table_name}', 1, 1, 1, '{now_time}', '{now_time}')")
        elif db1_type == 3:
            insert_sql = (f"insert into {table_entityconfig} (name,begin_id,step,current_id,create_time,update_time)"
                          f" values ('{table_name}', 1, 1, 1, to_timestamp('{now_time}','yyyy-mm-dd hh24:mi:ss'), to_timestamp('{now_time}','yyyy-mm-dd hh24:mi:ss'))")
        if con:
            RECV_DB.update_info(conn=con, sql=insert_sql)
        else:
            RECV_DB.update(insert_sql)
        current_id = 1
    else:
        current_id = int(current_id[0][0]) + 1
        update_sql = ""
        if db1_type == 1 or db1_type == 3 or db1_type == 4 or db1_type == 5:
            update_sql = f"update {table_entityconfig} set current_id={current_id},update_time=to_date('{now_time}','yyyy-mm-dd hh24:mi:ss') where NAME='{table_name}'"
        elif db1_type == 2:
            update_sql = f"update {table_entityconfig} set current_id={current_id},update_time='{now_time}' where NAME='{table_name}'"
        elif db1_type == 3:
            update_sql = f"update {table_entityconfig} set current_id={current_id},update_time=to_timestamp('{now_time}','yyyy-mm-dd hh24:mi:ss') where NAME='{table_name}'"
        if con:
            RECV_DB.update_info(conn=con, sql=update_sql)
        else:
            RECV_DB.update(update_sql)
    return str(current_id)
